
** build qwi files
if 1 {
	
	** county-quarter file 
	if 1 {
		
		local statelist ak al ar az ca co ct dc de fl ga hi ia id il in ks ky la ma md me mi mn mo ms mt nc nd ne nh nj nm nv ny oh ok or pa ri sc sd tn tx ut va vt wa wi wv wy
		local view 		sa_f_gc_ns_oslp_u
		local version 	R2020Q4
		local sex = 0
		* 	Note: sex code (0 = total, 1 = male, 2 = female)
		
		* State loop to create segments
		qui foreach state in `statelist' {
			
			noisily di "*** Creating data for state: `state'..."
			
			* Import csv
			insheet using "$Rep_smokelabor/1_build/qwi/raw/sa_f_gc_ns_oslp_u/qwi_`state'_`view'.csv", clear
			
			* Check one row is a county-sex-agegrp-industry-year-quarter
			gisid geography sex agegrp industry year quarter
			
			* Consistency checks
			if 1 {
				* Check equal numbers of by-sex cells
				* 	Label: https://lehd.ces.census.gov/pub/ak/latest_release/DVD-sa_f/label_sex.csv
				assert !missing(sex)
				assert (sex==0|sex==1|sex==2)
				qui foreach gsex in 0 1 2 {
					count if sex==`gsex'
					local N_sex`gsex' =r(N)
				}
				assert `N_sex0'==`N_sex1'
				assert `N_sex1'==`N_sex2'
			}
			
			* Keep only all-sex cells
			keep if sex==`sex'
			
			* Check equal numbers of by-agegrp cells
			* 	Label: https://lehd.ces.census.gov/pub/il/latest_release/DVD-sa_f/label_agegrp.csv
			if 1 {
				assert !missing(agegrp)
				assert (agegrp=="A00"|agegrp=="A01"|agegrp=="A02"|agegrp=="A03"|agegrp=="A04"|agegrp=="A05"|agegrp=="A06"|agegrp=="A07"|agegrp=="A08")
				qui foreach grp in A00 A01 A02 A03 A04 A05 A06 A07 A08 {
					count if agegrp== "`grp'"
					local N_grp`grp'=r(N)
					assert `N_grp`grp''==`N_grpA00'
				}
			}
			* Keep county level records
			keep if geo_level=="C"
			
			* Other consistency checks
			if 1 {
				assert periodicity=="Q"
				assert seasonadj=="U"
				assert ownercode=="A00"
				assert race=="A0"
				assert ethnicity=="A0"
				assert education=="E0"
				assert firmage==0
				assert firmsize==0
			}
			
			drop periodicity seasonadj ownercode race ethnicity education firmage firmsize geo_level sex
			
			* County-by-quarter variables
			keep if agegrp=="A00" & industry=="00"
			assert ind_level=="A"
			
			bys geography year quarter: assert _N==1
			
			gen f_1payroll = (spayroll==1)
			gen f_1emptotal = (semptotal==1)
			gen f_1hira = (shira==1)
			gen f_1sep = (ssep==1)
			gen f_1turnovrs = (sturnovrs==6)
				* Note: since turnover is calculated from hire and separation, the relevant flag is
				* 	"6 Value calculated from other released measures, no significant distortion"
			
			keep payroll hira sep emptotal turnovrs f_1payroll f_1emptotal f_1hira f_1sep f_1turnovrs geography year quarter
			rename geography countyfip
			compress
			saveold "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter.dta", replace
			
			noisily di " "
			noisily di " "
			noisily di " "
			noisily di " "
			noisily di " "
		} 
		*** End of state-loop
		
		* Append state segments
		clear
		foreach state in `statelist' {
			append using "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter.dta"
			erase "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter.dta"
		}
		gisid countyfip year quarter
				
		tabstat payroll, by(year) s(sum min p50 max N)
		tabstat f_1*, by(year)
		
		rename (year quarter payroll hira sep emptotal turnovrs) (rfrnc_yr rfrnc_qtroy qwi_payroll qwi_hira qwi_sep qwi_emptotal qwi_turnovrs)
		
		lab var countyfip 		"FIPs county"
		lab var rfrnc_yr 		"Reference year"
		lab var qwi_payroll 	"QWI: total payroll for all jobs ($)"
		lab var qwi_hira 		"QWI: workers started new jobs (persons)"
		lab var qwi_sep 		"QWI: workers separated (persons)"
		lab var qwi_emptotal 	"QWI: workers employed (persons)"
		lab var qwi_turnovrs 	"QWI: rate at which stable jobs begin and end"
		lab var f_1payroll 		"Fraction of data flagged 1 (OK, fuzzed value released), payroll"
		lab var f_1emptotal 	"Fraction of data flagged 1 (OK, fuzzed value released), emptotal"
		lab var f_1hira 		"Fraction of data flagged 1 (OK, fuzzed value released), hira"
		lab var f_1sep 			"Fraction of data flagged 1 (OK, fuzzed value released), sep"
		lab var f_1turnovrs 	"Fraction of data flagged 1 (OK, fuzzed value released), turnovrs"
		
		compress
		saveold "$Rep_smokelabor/1_build/qwi/proc/qwi_county_quarterly.dta", replace 
		
	}
	** end of module
	
	** county-quarter-agegrp file 
	if 1 {
		
			
		local statelist ak al ar az ca co ct dc de fl ga hi ia id il in ks ky la ma md me mi mn mo ms mt nc nd ne nh nj nm nv ny oh ok or pa ri sc sd tn tx ut va vt wa wi wv wy
		local view 		sa_f_gc_ns_oslp_u
		local version 	R2020Q4
		local sex = 0
		* 	Note: sex code (0 = total, 1 = male, 2 = female)
		
		* State loop to create segments
		qui foreach state in `statelist' {
			
			noisily di "*** Creating data for state: `state'..."
			
			* Import csv
			insheet using "$Rep_smokelabor/1_build/qwi/raw/sa_f_gc_ns_oslp_u/qwi_`state'_`view'.csv", clear
			
			* Check one row is a county-sex-agegrp-industry-year-quarter
			gisid geography sex agegrp industry year quarter
			
			* Consistency checks
			if 0 {
				* Check equal numbers of by-sex cells
				* 	Label: https://lehd.ces.census.gov/pub/ak/latest_release/DVD-sa_f/label_sex.csv
				assert !missing(sex)
				assert (sex==0|sex==1|sex==2)
				qui foreach gsex in 0 1 2 {
					count if sex==`gsex'
					local N_sex`gsex' =r(N)
				}
				assert `N_sex0'==`N_sex1'
				assert `N_sex1'==`N_sex2'
			}
			
			* Keep only all-sex cells
			keep if sex==`sex'
			
			* Check equal numbers of by-agegrp cells
			* 	Label: https://lehd.ces.census.gov/pub/il/latest_release/DVD-sa_f/label_agegrp.csv
			* Label (2021-9-26): https://lehd.ces.census.gov/data/schema/latest/lehd_public_use_schema.html#_identifiers_for_qwi
			if 0 {
				assert !missing(agegrp)
				assert (agegrp=="A00"|agegrp=="A01"|agegrp=="A02"|agegrp=="A03"|agegrp=="A04"|agegrp=="A05"|agegrp=="A06"|agegrp=="A07"|agegrp=="A08")
				qui foreach grp in A00 A01 A02 A03 A04 A05 A06 A07 A08 {
					count if agegrp== "`grp'"
					local N_grp`grp'=r(N)
					assert `N_grp`grp''==`N_grpA00'
				}
			}
			
			
			* Keep county level records
			keep if geo_level=="C"
			
			* Other consistency checks
			if 1 {
				assert periodicity=="Q"
				assert seasonadj=="U"
				assert ownercode=="A00"
				assert race=="A0"
				assert ethnicity=="A0"
				assert education=="E0"
				assert firmage==0
				assert firmsize==0
			}
			
			drop periodicity seasonadj ownercode race ethnicity education firmage firmsize geo_level sex
			
			* County-by-quarter variables
			*keep if agegrp=="A00" & industry=="00"
			* !! keep 55+ 
			keep if (agegrp=="A04"|agegrp=="A05"|agegrp=="A06"|agegrp=="A07"|agegrp=="A08") 
			
			keep if industry=="00"
			assert ind_level=="A"
			
			gisid geography year quarter agegrp
			
			gen f_1payroll = (spayroll==1)
			gen f_1emptotal = (semptotal==1)
			gen f_1hira = (shira==1)
			gen f_1sep = (ssep==1)
			gen f_1turnovrs = (sturnovrs==6)
				* Note: since turnover is calculated from hire and separation, the relevant flag is
				* 	"6 Value calculated from other released measures, no significant distortion"
			
			keep payroll hira sep emptotal turnovrs f_1payroll f_1emptotal f_1hira f_1sep f_1turnovrs geography year quarter agegrp
			
			rename geography countyfip
			compress
			saveold "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter_agegrp.dta", replace
			
			noisily di " "
			noisily di " "
			noisily di " "
			noisily di " "
			noisily di " "
		} 
		*** End of state-loop
		
		* Append state segments
		clear
		foreach state in `statelist' {
			append using "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter_agegrp.dta"
			erase "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter_agegrp.dta"
		}
		gisid countyfip year quarter agegrp
				
		tabstat payroll, by(year) s(sum min p50 max N)
		tabstat f_1*, by(year)
		
		rename (year quarter payroll hira sep emptotal turnovrs) (rfrnc_yr rfrnc_qtroy qwi_payroll qwi_hira qwi_sep qwi_emptotal qwi_turnovrs)
		
		lab var countyfip 		"FIPs county"
		lab var rfrnc_yr 		"Reference year"
		lab var qwi_payroll 	"QWI 55+: total payroll for all jobs ($)"
		lab var qwi_hira 		"QWI 55+: workers started new jobs (persons)"
		lab var qwi_sep 		"QWI 55+: workers separated (persons)"
		lab var qwi_emptotal 	"QWI 55+: workers employed (persons)"
		lab var qwi_turnovrs 	"QWI 55+: rate at which stable jobs begin and end"
		lab var f_1payroll 		"Fraction 55+ of data flagged 1 (OK, fuzzed value released), payroll"
		lab var f_1emptotal 	"Fraction 55+ of data flagged 1 (OK, fuzzed value released), emptotal"
		lab var f_1hira 		"Fraction 55+ of data flagged 1 (OK, fuzzed value released), hira"
		lab var f_1sep 			"Fraction 55+ of data flagged 1 (OK, fuzzed value released), sep"
		lab var f_1turnovrs 	"Fraction 55+ of data flagged 1 (OK, fuzzed value released), turnovrs"
		lab var agegrp 			"A04=25-34,..,A08=65plus"
		
		compress
		saveold "$Rep_smokelabor/1_build/qwi/proc/qwi_agegrp_county_quarterly.dta", replace 
	}
	** end of module
	
	** county-quarter-naics2 file
	if 1 {
		local statelist ak al ar az ca co ct dc de fl ga hi ia id il in ks ky la ma md me mi mn mo ms mt nc nd ne nh nj nm nv ny oh ok or pa ri sc sd tn tx ut va vt wa wi wv wy
		local view 		sa_f_gc_ns_oslp_u
		local version 	R2020Q4
		local sex = 0
		* 	Note: sex code (0 = total, 1 = male, 2 = female)
		
		* State loop to create segments
		qui foreach state in `statelist' {
			
			noisily di "*** Creating data for state: `state'..."
			
			* Import csv
			insheet using "$Rep_smokelabor/1_build/qwi/raw/sa_f_gc_ns_oslp_u/qwi_`state'_`view'.csv", clear
			
			* Check one row is a county-sex-agegrp-industry-year-quarter
			gisid geography sex agegrp industry year quarter
						
			* Keep only all-sex cells
			keep if sex==`sex'
			
			* Keep county level records
			keep if geo_level=="C"
			
			* consistency checks
			if 1 {
				assert periodicity=="Q"
				assert seasonadj=="U"
				assert ownercode=="A00"
				assert race=="A0"
				assert ethnicity=="A0"
				assert education=="E0"
				assert firmage==0
				assert firmsize==0
			}
			
			drop periodicity seasonadj ownercode race ethnicity education firmage firmsize geo_level sex
			
			* County-by-quarter variables
			* keep all-age cells
			keep if (agegrp=="A00") 
			
			* drop all-industry cells 
			drop if industry=="00"
			
			gisid geography year quarter industry
			
			gen f_1payroll = (spayroll==1)
			gen f_1emptotal = (semptotal==1)
			gen f_1hira = (shira==1)
			gen f_1sep = (ssep==1)
			gen f_1turnovrs = (sturnovrs==6)
				* Note: since turnover is calculated from hire and separation, the relevant flag is
				* 	"6 Value calculated from other released measures, no significant distortion"
			
			keep payroll hira sep emptotal turnovrs f_1payroll f_1emptotal f_1hira f_1sep f_1turnovrs geography year quarter industry
			
			rename geography countyfip
			compress
			saveold "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter_naics2.dta", replace
						
			noisily di " "
			noisily di " "
			noisily di " "
			noisily di " "
			noisily di " "
		} 
		*** End of state-loop
		
		* Append state segments
		clear
		foreach state in `statelist' {
			append using "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter_naics2.dta"
			erase "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter_naics2.dta"
		}
		gisid countyfip year quarter industry
				
		tabstat payroll, by(year) s(sum min p50 max N)
		tabstat f_1*, by(year)
		
		rename (year quarter payroll hira sep emptotal turnovrs) (rfrnc_yr rfrnc_qtroy qwi_payroll qwi_hira qwi_sep qwi_emptotal qwi_turnovrs)
		
		lab var countyfip 		"FIPs county"
		lab var rfrnc_yr 		"Reference year"
		lab var qwi_payroll 	"QWI 55+: total payroll for all jobs ($)"
		lab var qwi_hira 		"QWI 55+: workers started new jobs (persons)"
		lab var qwi_sep 		"QWI 55+: workers separated (persons)"
		lab var qwi_emptotal 	"QWI 55+: workers employed (persons)"
		lab var qwi_turnovrs 	"QWI 55+: rate at which stable jobs begin and end"
		lab var f_1payroll 		"Fraction 55+ of data flagged 1 (OK, fuzzed value released), payroll"
		lab var f_1emptotal 	"Fraction 55+ of data flagged 1 (OK, fuzzed value released), emptotal"
		lab var f_1hira 		"Fraction 55+ of data flagged 1 (OK, fuzzed value released), hira"
		lab var f_1sep 			"Fraction 55+ of data flagged 1 (OK, fuzzed value released), sep"
		lab var f_1turnovrs 	"Fraction 55+ of data flagged 1 (OK, fuzzed value released), turnovrs"
		lab var industry 		"2-digit naics"
		
		compress
		saveold "$Rep_smokelabor/1_build/qwi/proc/qwi_naics2_county_quarterly.dta", replace 
	
	}
	** end of module

	** county-quarter-naics3 (ag) file 
	if 1 {
		
		local statelist ak al ar az ca co ct dc de fl ga hi ia id il in ks ky la ma md me mi mn mo ms mt nc nd ne nh nj nm nv ny oh ok or pa ri sc sd tn tx ut va vt wa wi wv wy
		local view 		sa_f_gc_n3_oslp_u
		local version 	R2021Q2
		local sex = 0
		* 	Note: sex code (0 = total, 1 = male, 2 = female)
	
		qui foreach state in `statelist' {
			
			noisily di "*** Creating data for state: `state'..."
			
			* Import csv
			insheet using "$Rep_smokelabor/1_build/qwi/raw/sa_f_gc_n3_oslp_u/qwi_`state'_`view'.csv", clear
			
			* Check one row is a county-sex-agegrp-industry-year-quarter
			gisid geography sex agegrp industry year quarter
			
			* Consistency checks
			if 1 {
				* Check equal numbers of by-sex cells
				* 	Label: https://lehd.ces.census.gov/pub/ak/latest_release/DVD-sa_f/label_sex.csv
				assert !missing(sex)
				assert (sex==0|sex==1|sex==2)
				qui foreach gsex in 0 1 2 {
					count if sex==`gsex'
					local N_sex`gsex' =r(N)
				}
				assert `N_sex0'==`N_sex1'
				assert `N_sex1'==`N_sex2'
			}
			
			* Keep only all-sex cells
			keep if sex==`sex'
			
			* Check equal numbers of by-agegrp cells
			* 	Label: https://lehd.ces.census.gov/pub/il/latest_release/DVD-sa_f/label_agegrp.csv
			* Label (2021-9-26): https://lehd.ces.census.gov/data/schema/latest/lehd_public_use_schema.html#_identifiers_for_qwi
			if 1 {
				assert !missing(agegrp)
				assert (agegrp=="A00"|agegrp=="A01"|agegrp=="A02"|agegrp=="A03"|agegrp=="A04"|agegrp=="A05"|agegrp=="A06"|agegrp=="A07"|agegrp=="A08")
				qui foreach grp in A00 A01 A02 A03 A04 A05 A06 A07 A08 {
					count if agegrp== "`grp'"
					local N_grp`grp'=r(N)
					assert `N_grp`grp''==`N_grpA00'
				}
			}
			
			* Keep county level records
			keep if geo_level=="C"
			
			* Other consistency checks
			if 1 {
				assert periodicity=="Q"
				assert seasonadj=="U"
				assert ownercode=="A00"
				assert race=="A0"
				assert ethnicity=="A0"
				assert education=="E0"
				assert firmage==0
				assert firmsize==0
			}
			
			drop periodicity seasonadj ownercode race ethnicity education firmage firmsize geo_level sex
			
			* County-by-quarter variables
			* keep all-age cells
			keep if (agegrp=="A00") 
			
			* drop all-industry cells 
			drop if industry==0
			
			gisid geography year quarter industry
			
			gen f_1payroll = (spayroll==1)
			gen f_1emptotal = (semptotal==1)
			gen f_1hira = (shira==1)
			gen f_1sep = (ssep==1)
			gen f_1turnovrs = (sturnovrs==6)
				* Note: since turnover is calculated from hire and separation, the relevant flag is
				* 	"6 Value calculated from other released measures, no significant distortion"
			
			keep payroll hira sep emptotal turnovrs f_1payroll f_1emptotal f_1hira f_1sep f_1turnovrs geography year quarter industry
			
			rename geography countyfip
			compress
			saveold "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter_naics3.dta", replace
			
			* Clean up
			*erase `proc'/qwi/qwi_`state'_`view'.csv
			
			noisily di " "
			noisily di " "
			noisily di " "
			noisily di " "
			noisily di " "
		} 
		*** End of state-loop
		
		* Append state segments
		clear
		foreach state in `statelist' {
			append using "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter_naics3.dta"
			erase "$Rep_smokelabor/1_build/qwi/proc/tmpfile_`state'_cnty_quarter_naics3.dta"
		}
		gisid countyfip year quarter industry
				
		gstats tabstat emptotal, by(year) s(sum min p50 max count)
		gstats tabstat f_1*, by(year) col(variables) s(mean)
		
		rename (year quarter payroll hira sep emptotal turnovrs) (rfrnc_yr rfrnc_qtroy qwi_payroll qwi_hira qwi_sep qwi_emptotal qwi_turnovrs)
		
		lab var countyfip 		"FIPs county"
		lab var rfrnc_yr 		"Reference year"
		lab var qwi_payroll 	"QWI 55+: total payroll for all jobs ($)"
		lab var qwi_hira 		"QWI 55+: workers started new jobs (persons)"
		lab var qwi_sep 		"QWI 55+: workers separated (persons)"
		lab var qwi_emptotal 	"QWI 55+: workers employed (persons)"
		lab var qwi_turnovrs 	"QWI 55+: rate at which stable jobs begin and end"
		lab var f_1payroll 		"Fraction 55+ of data flagged 1 (OK, fuzzed value released), payroll"
		lab var f_1emptotal 	"Fraction 55+ of data flagged 1 (OK, fuzzed value released), emptotal"
		lab var f_1hira 		"Fraction 55+ of data flagged 1 (OK, fuzzed value released), hira"
		lab var f_1sep 			"Fraction 55+ of data flagged 1 (OK, fuzzed value released), sep"
		lab var f_1turnovrs 	"Fraction 55+ of data flagged 1 (OK, fuzzed value released), turnovrs"
		lab var industry 		"3-digit naics"
		
		compress
		saveold "$Rep_smokelabor/1_build/qwi/proc/qwi_naics3_county_quarterly.dta", replace 
		
		* ag 
		keep if industry<200 
		compress
		saveold "$Rep_smokelabor/1_build/qwi/proc/qwi_naics3_ag_county_quarterly", replace 
		
	}
	** end of module
	
}	